

import pandas as pd
from datetime import datetime

def generate_sql_from_excel(file_path, sheet_index=0,start_oid=2000000021020):
    # 读取Excel文件
    df = pd.read_excel(file_path,sheet_name=sheet_index, dtype={'属性编码': str})

    # 生成SQL语句列表
    sql_statements = []
    for index, row in df.iterrows():
        # 获取当前时间作为createTime和editTime
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        # 判断 row['设备A码'] 是否为空
        if pd.isna(row['设备A码']):
            continue
        # 判断 row['台位编码'] 是否为空
        if pd.isna(row['台位编码']):
            continue

        # 构造SQL语句  设备A码	台位编码	*属性名称	属性编码	工位
        sql = f"INSERT INTO midea_lims_new.equipscadanode " \
              f"(oid, createTime, editTime, equipmentNumber, equipACode, checkCode, signalType, workLocation, blockName, buId) " \
              f"VALUES({start_oid + index}, '{current_time}', '{current_time}', '{row['台位编码']}', " \
              f"'{row['设备A码']}', '{str(row['属性编码'])}', '{row['*属性名称']}', '{row['工位']}', 'MITBG', 21);"

        sql_statements.append(sql)

    return sql_statements

# 使用示例
file_path = 'D:\\Users\\ex_yejing1\\Desktop\\压缩机设备联机20250513\\40个设备信号测点配置表.xlsx'  # 替换为你的Excel文件路径
sqls = generate_sql_from_excel(file_path,4,2000000021200)

# 打印生成的SQL语句
for sql in sqls:
    print(sql)
    # 打印sqls的条数

print(len(sqls))




